// Staff fields helper - unify dynamic expressions for people and maintenance time
// This file exports pure helpers that work with the shared dbPool instance passed in by callers.

async function getPeopleExpr(dbPool){
  try{
    const rows = await dbPool.query('PRAGMA table_info(pph_records)');
    const set = new Set((rows||[]).map(r=>String(r.name).toLowerCase()));
    const has = (c)=>set.has(String(c).toLowerCase());

    // Priority chain
    if (has('total_people')){
      return { expr: 'total_people', columns: ['total_people'] };
    }
    if (has('online_staff') && has('offline_staff')){
      return { expr: 'COALESCE(online_staff,0)+COALESCE(offline_staff,0)', columns: ['online_staff','offline_staff'] };
    }
    if (has('working_people') && has('offline_staff')){
      return { expr: 'COALESCE(working_people,0)+COALESCE(offline_staff,0)', columns: ['working_people','offline_staff'] };
    }
    if (has('total_staff')){
      return { expr: 'COALESCE(total_staff,0)', columns: ['total_staff'] };
    }
    return { expr: '0', columns: [] };
  }catch(e){
    try{ console.warn('getPeopleExpr failed:', e && (e.message||e)); }catch(_){}
    return { expr: '0', columns: [] };
  }
}

async function getMaintenanceExpr(dbPool){
  try{
    const rows = await dbPool.query('PRAGMA table_info(pph_records)');
    const set = new Set((rows||[]).map(r=>String(r.name).toLowerCase()));
    const hasMaint = set.has('maintenance_time');
    return { expr: hasMaint ? 'COALESCE(maintenance_time,0)' : '0', columns: hasMaint?['maintenance_time']:[] };
  }catch(e){
    try{ console.warn('getMaintenanceExpr failed:', e && (e.message||e)); }catch(_){}
    return { expr: '0', columns: [] };
  }
}

async function getTargetColsPresence(dbPool){
  try{
    const rows = await dbPool.query('PRAGMA table_info(pph_records)');
    const set = new Set((rows||[]).map(r=>String(r.name).toLowerCase()));
    return {
      hasTarget: set.has('target_pph'),
      hasEstimate: set.has('estimate_pph'),
      hasEfficiency: set.has('efficiency_rate'),
      hasTargetRecordDate: set.has('target_record_date')
    };
  }catch(e){
    return { hasTarget:false, hasEstimate:false, hasEfficiency:false, hasTargetRecordDate:false };
  }
}

module.exports = { getPeopleExpr, getMaintenanceExpr, getTargetColsPresence };
